package ro.fortech.peaa.repository.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import ro.fortech.peaa.domain.Employee;
import ro.fortech.peaa.repository.api.IEmployeeMapper;

public class JdbcEmployeeLockingMapper extends JdbcEmployeeMapper {
	
	/**
	 * The only thing this method does and the corresponding method from the
	 * JdbcEmployeeMapper doesn't is that selects the version of the object.
	 */
	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.impl.JdbcEmployeeMapper#find(java.lang.Integer)
	 */
	@Override
	public Employee find(Integer id) throws Exception {
		logger.info("Find employee with id: " + id);
		List<Employee> employees = getSimpleJdbcTemplate().query("SELECT " 
				+ IEmployeeMapper.ID + ", " 
				+ IEmployeeMapper.FIRST_NAME + "," 
				+ IEmployeeMapper.LAST_NAME + ", "
				+ IEmployeeMapper.EMAIL + ", "
				+ IEmployeeMapper.PHONE_NUBER + ", "
				+ IEmployeeMapper.HIRE_DATE +", "
				+ IEmployeeMapper.JOB_ID + ", "
				+ IEmployeeMapper.SALARY + ", "
				+ IEmployeeMapper.COMMISSION_PCT + ", "
				+ IEmployeeMapper.MANAGER_ID + ", "
				+ IEmployeeMapper.DEPARTMENT_ID +", "
				+ IEmployeeMapper.VERSION
				+ " FROM " + IEmployeeMapper.TABLE + " WHERE " + IEmployeeMapper.ID + "=" + id, new EmployeeAttrMapper()); 

		if (!employees.isEmpty()) {
			logger.info("Employee with id: " + id + " found.");
			return employees.get(0);
		} else {
			logger.info("Employee with id: " + id + " not found.");
			return null;
		}

	}

	/**
	 * The only thing this method does and the corresponding method from the
	 * JdbcEmployeeMapper doesn't is that selects the version of the objects.
	 */
	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.impl.JdbcEmployeeMapper#findAll()
	 */
	@Override
	public List<Employee> findAll() throws Exception {
		logger.info("Get all employees");
		List<Employee> employees = getSimpleJdbcTemplate().query("SELECT " 
				+ IEmployeeMapper.ID + ", " 
				+ IEmployeeMapper.FIRST_NAME + "," 
				+ IEmployeeMapper.LAST_NAME + ", "
				+ IEmployeeMapper.EMAIL + ", "
				+ IEmployeeMapper.PHONE_NUBER + ", "
				+ IEmployeeMapper.HIRE_DATE +", "
				+ IEmployeeMapper.JOB_ID + ", "
				+ IEmployeeMapper.SALARY + ", "
				+ IEmployeeMapper.COMMISSION_PCT + ", "
				+ IEmployeeMapper.MANAGER_ID + ", "
				+ IEmployeeMapper.DEPARTMENT_ID  + ", "
				+ IEmployeeMapper.VERSION
				+ " FROM " + IEmployeeMapper.TABLE, new EmployeeAttrMapper());
		logger.info("Found " + employees.size() + " employees");
		return employees;
	}

	/**
	 * The only thing this method does and the corresponding method from the
	 * JdbcEmployeeMapper doesn't is that inserts the version of the object.
	 */
	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.impl.JdbcEmployeeMapper#insert(ro.fortech.peaa.domain.Employee)
	 */
	@Override
	public void insert(Employee employee) throws Exception {
		logger.info("Insert employee");
		int id = getSimpleJdbcTemplate().queryForInt("SELECT " + IEmployeeMapper.TABLE + "_SEQ.nextVal FROM dual", new HashMap<String, String>());
		logger.info("Next employee id: " + id);
		employee.setId(id);
		employee.setVersion(0);
		
		String query = "INSERT INTO " + IEmployeeMapper.TABLE + " ("
		+ IEmployeeMapper.ID +", "
		+ IEmployeeMapper.FIRST_NAME + ", "
		+ IEmployeeMapper.LAST_NAME + ", "
		+ IEmployeeMapper.EMAIL + ", "
		+ IEmployeeMapper.PHONE_NUBER + ", "
		+ IEmployeeMapper.HIRE_DATE + ", "
		+ IEmployeeMapper.JOB_ID + ", "
		+ IEmployeeMapper.SALARY + ", "
		+ IEmployeeMapper.COMMISSION_PCT + ", "
		+ IEmployeeMapper.MANAGER_ID + ", "
		+ IEmployeeMapper.DEPARTMENT_ID + "," 
		+ IEmployeeMapper.VERSION +") VALUES  ("
		+ ":" + IEmployeeMapper.ID  +", " 
		+ ":" + IEmployeeMapper.FIRST_NAME + ", "
		+ ":" + IEmployeeMapper.LAST_NAME + ", "
		+ ":" + IEmployeeMapper.EMAIL + ", "
		+ ":" + IEmployeeMapper.PHONE_NUBER + ", "
		+ ":" + IEmployeeMapper.HIRE_DATE + ", "
		+ ":" + IEmployeeMapper.JOB_ID + ", "
		+ ":" + IEmployeeMapper.SALARY + ", "
		+ ":" + IEmployeeMapper.COMMISSION_PCT + ", "
		+ ":" + IEmployeeMapper.MANAGER_ID + ", "
		+ ":" + IEmployeeMapper.DEPARTMENT_ID + ", " 
		+ ":" + IEmployeeMapper.VERSION + " )";
		logger.info("insert query: " + query);
		int count = getSimpleJdbcTemplate().update(query, 
				new MapSqlParameterSource()
				.addValue(IEmployeeMapper.ID, employee.getId())
				.addValue(IEmployeeMapper.FIRST_NAME, employee.getFirstName())
				.addValue(IEmployeeMapper.LAST_NAME, employee.getLastName())
				.addValue(IEmployeeMapper.EMAIL, employee.getEmail())
				.addValue(IEmployeeMapper.PHONE_NUBER, employee.getPhoneNumber())
				.addValue(IEmployeeMapper.HIRE_DATE, new java.sql.Date(employee.getHireDate().getTime()))
				.addValue(IEmployeeMapper.JOB_ID, employee.getJobId())
				.addValue(IEmployeeMapper.SALARY, employee.getSalary())
				.addValue(IEmployeeMapper.COMMISSION_PCT, employee.getCommissionPct())
				.addValue(IEmployeeMapper.MANAGER_ID, employee.getManagerId())
				.addValue(IEmployeeMapper.DEPARTMENT_ID, employee.getDepartmentId())
				.addValue(IEmployeeMapper.VERSION, employee.getVersion()));
		logger.info("Rows affected: " + count);
	}

	/**
	 * The only thing this method does and the corresponding method from the
	 * JdbcEmployeeMapper doesn't is that it updates only if the object is 
	 * consistent
	 */
	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.impl.JdbcEmployeeMapper#update(ro.fortech.peaa.domain.Employee)
	 */
	@Override
	public void update(Employee employee) throws Exception {
		logger.info("update employee " + employee.getHireDate());
		Integer currentVersion = employee.getVersion();
		String query = "UPDATE "
			+ IEmployeeMapper.TABLE + " SET "
			+ IEmployeeMapper.FIRST_NAME + "=:" + IEmployeeMapper.FIRST_NAME + ", "
			+ IEmployeeMapper.LAST_NAME + "=:" + IEmployeeMapper.LAST_NAME + ", "
			+ IEmployeeMapper.EMAIL + "=:" + IEmployeeMapper.EMAIL + ", "
			+ IEmployeeMapper.PHONE_NUBER + "=:" + IEmployeeMapper.PHONE_NUBER + ", "
			+ IEmployeeMapper.HIRE_DATE + "=:" + IEmployeeMapper.HIRE_DATE + ", "
			+ IEmployeeMapper.JOB_ID + "=:" + IEmployeeMapper.JOB_ID + ", "
			+ IEmployeeMapper.SALARY + "=:" + IEmployeeMapper.SALARY + ", "
			+ IEmployeeMapper.COMMISSION_PCT + "=:" + IEmployeeMapper.COMMISSION_PCT + ", "
			+ IEmployeeMapper.MANAGER_ID + "=:" + IEmployeeMapper.MANAGER_ID + ", "
			+ IEmployeeMapper.DEPARTMENT_ID + "=:" + IEmployeeMapper.DEPARTMENT_ID + ", "
			+ IEmployeeMapper.VERSION + "=:" + "NEW_" + IEmployeeMapper.VERSION
			+ " WHERE " + IEmployeeMapper.ID + "=:" + IEmployeeMapper.ID
			+ " AND " + IEmployeeMapper.VERSION + "=:" + IEmployeeMapper.VERSION;
		logger.info("update query: " + query);
		logger.info(employee.getId()
				+ employee.getFirstName() + employee.getLastName() 
				+ employee.getEmail() + employee.getPhoneNumber()
				+ new java.sql.Date(employee.getHireDate().getTime())
				+ employee.getJobId()
				+ employee.getSalary()
				+ employee.getCommissionPct()
				+ employee.getManagerId()
				+ employee.getDepartmentId()
				+ "VERSION: " + employee.getVersion()); 
		int count = getSimpleJdbcTemplate().update(query,
				new MapSqlParameterSource().addValue(IEmployeeMapper.ID, employee.getId())
				.addValue(IEmployeeMapper.FIRST_NAME, employee.getFirstName())
				.addValue(IEmployeeMapper.LAST_NAME, employee.getLastName())
				.addValue(IEmployeeMapper.EMAIL, employee.getEmail())
				.addValue(IEmployeeMapper.PHONE_NUBER, employee.getPhoneNumber())
				.addValue(IEmployeeMapper.HIRE_DATE, new java.sql.Date(employee.getHireDate().getTime()))
				.addValue(IEmployeeMapper.JOB_ID, employee.getJobId())
				.addValue(IEmployeeMapper.SALARY, employee.getSalary())
				.addValue(IEmployeeMapper.COMMISSION_PCT, employee.getCommissionPct())
				.addValue(IEmployeeMapper.MANAGER_ID, employee.getManagerId())
				.addValue(IEmployeeMapper.DEPARTMENT_ID, employee.getDepartmentId())
				.addValue("NEW_" + IEmployeeMapper.VERSION, currentVersion + 1)
				.addValue(IEmployeeMapper.VERSION, currentVersion));
		logger.info("Rows affected: " + count);
		employee.setVersion(currentVersion + 1);
		// Check if update was made. If not, throw concurrency error
		if (count == 0) {
			throw new SQLException("Concurrency error! Please step back!");
		}
	}

	private static class EmployeeAttrMapper implements ParameterizedRowMapper<Employee> {

		public Employee mapRow(ResultSet rs, int arg1) throws SQLException {
			Employee employee  = new Employee();
			employee.setId(rs.getInt(IEmployeeMapper.ID));
			employee.setFirstName(rs.getString(IEmployeeMapper.FIRST_NAME));
			employee.setLastName(rs.getString(IEmployeeMapper.LAST_NAME));
			employee.setEmail(rs.getString(IEmployeeMapper.EMAIL));
			employee.setPhoneNumber(rs.getString(IEmployeeMapper.PHONE_NUBER));
			employee.setHireDate(rs.getDate(IEmployeeMapper.HIRE_DATE));
			employee.setJobId(rs.getString(IEmployeeMapper.JOB_ID));
			employee.setSalary(rs.getDouble(IEmployeeMapper.SALARY));
			employee.setCommissionPct(rs.getDouble(IEmployeeMapper.COMMISSION_PCT));
			employee.setManagerId(rs.getInt(IEmployeeMapper.MANAGER_ID));
			employee.setDepartmentId(rs.getInt(IEmployeeMapper.DEPARTMENT_ID));
			employee.setVersion(rs.getInt(IEmployeeMapper.VERSION));
			return employee;
		}
	}

}
